﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;

using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BLL;
using System.IO;
using Aspose.Cells;
public partial class Admin_ChuHUo : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!AdminCheck.Check(this, 1))
        {
            Response.Redirect(AdminCheck.RedirectPage);
            return;
        }
        if (!Page.IsPostBack)
        {


        }

        //离港飞行中 

    }
    protected void gsbnt_Click(object sender, ImageClickEventArgs e)
    {
        if (datetxt.Value.Trim() == "")
        {

            Tools.OpenWindow(this, "请选择日期");
            return;
        }

        string name = DateTime.Now.ToString("yyyyMMddHHmmss") + Session["uid"].ToString();

        string sql = "  select a.TrackingNo ,  a.AerialNo ,a.AccountNo,a.Receiver,a.ToPhoneNo,a.ToAddress, a.ChargedWt ,a.sep ,a.FlightNo from   Tbl_WayBill a left join  Tbl_BillStateRec b on a.TrackingNo=b.SepNo ";
        sql += "    where a.TrackingNo<>'' and b.StateID=3 and   sep=0 and b.Date >= cast('" + datetxt.Value.Trim() + " 00:00:00 ' as datetime  ) and  b.Date < = cast('" + datetxt.Value.Trim() + " 23:59:059 ' as datetime) ";

        sql += "union";

        sql += " select  distinct a.TrackingNo , c.AerialNo ,c.AccountNo,c.Receiver,c.ToPhoneNo,c.ToAddress, c.ChargedWt ,c.sep  ,c.FlightNo from   Tbl_SepBill a left join  Tbl_BillStateRec b on a.SepNo=b.SepNo left join Tbl_WayBill c on a.TrackingNo=c.TrackingNo ";

        sql += "    where a.TrackingNo<>'' and a.SepNo<>''  and b.StateID=3 and b.Date >= cast('" + datetxt.Value.Trim() + " 00:00:00 ' as datetime  ) and  b.Date < = cast('" + datetxt.Value.Trim() + " 23:59:059 ' as datetime) ";



        //string sql = "  select a.AerialNo, a.TrackingNo ,a.AccountNo,a.Receiver,a.ToPhoneNo,a.ToAddress, a.ChargedWt ,a.sep from   Tbl_WayBill a left join  Tbl_BillStateRec b on a.TrackingNo=b.SepNo ";
        //sql += "    where a.TrackingNo<>'' and b.StateID=3 and b.Date >= cast('" + datetxt.Value.Trim() + " 00:00:00 ' as datetime  ) and  b.Date < = cast('" + datetxt.Value.Trim() + " 23:59:059 ' as datetime) ";



        DataTable dt = new PublicSqlService().RuntSQL(sql);

        Workbook wb = new Workbook();
        wb.Worksheets.Clear();
        wb.Worksheets.Add("公司出货报表-" + DateTime.Parse(datetxt.Value).ToLongDateString());//New Worksheet1是Worksheet的name
        Worksheet ws = wb.Worksheets[0];


        //1-12 列合并
        ws.Cells.Merge(0, 0, 1, 8);
        ws.Cells[0, 0].PutValue("公司出货报表-" + DateTime.Parse(datetxt.Value).ToLongDateString());
        ws.Cells[1, 0].PutValue("账号");
        ws.Cells[1, 1].PutValue("航空号");
        ws.Cells[1, 2].PutValue("运单号");
        ws.Cells[1, 3].PutValue("收件人");
        ws.Cells[1, 4].PutValue("收件地址");
        ws.Cells[1, 5].PutValue("电话");
        ws.Cells[1, 6].PutValue("品名");

        ws.Cells[1, 7].PutValue("重量");
        ws.Cells[1, 8].PutValue("参考号码");
        int count = 1;
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            count = count + 1;
            //    ws.Cells[count, 0].PutValue("");
            count = count + 1;
            // ws.Cells[count, 0].PutValue("");
            count = count + 1;
            ws.Cells[count, 0].PutValue(dt.Rows[i]["AccountNo"]);
            ws.Cells[count, 1].PutValue(dt.Rows[i]["AerialNo"]);
            ws.Cells[count, 2].PutValue(dt.Rows[i]["TrackingNo"]);

            ws.Cells[count, 3].PutValue(dt.Rows[i]["Receiver"]);
            ws.Cells[count, 4].PutValue(dt.Rows[i]["ToAddress"]);
            ws.Cells[count, 5].PutValue(dt.Rows[i]["ToPhoneNo"]);


            string pname = "";
            GetNameAndWeight(dt.Rows[i]["TrackingNo"].ToString(), ref pname);
            ws.Cells[count, 6].PutValue(pname);
            ws.Cells[count, 7].PutValue(dt.Rows[i]["ChargedWt"]);
            ws.Cells[count, 8].PutValue(dt.Rows[i]["FlightNo"].ToString());
            if (dt.Rows[i]["sep"].ToString().Trim() != "0")
            {
                //有分单
                string sql2 = @"select a.AerialNo,  a. SepNo ,a.ACNo as AccountNo,a.CHNReceiver as Receiver,a.CHNTelNo as ToPhoneNo,a.CHNAddress as ToAddress , a.Weight as ChargedWt,a.FlightNo  from   Tbl_SepBill a  left join  Tbl_BillStateRec b on a.SepNo=b.SepNo  where a.TrackingNo='" + dt.Rows[i]["TrackingNo"] + "' and b.StateID=3 and b.Date >= cast('" + datetxt.Value.Trim() + " 00:00:00 ' as datetime  ) and  b.Date < = cast('" + datetxt.Value.Trim() + " 23:59:059 ' as datetime)   order by  a.AerialNo";
                DataTable dt2 = new PublicSqlService().RuntSQL(sql2);
                if (dt2.Rows.Count != 0)
                {
                    for (int y = 0; y < dt2.Rows.Count; y++)
                    {
                        count = count + 1;
                        ws.Cells[count, 0].PutValue(dt2.Rows[y]["AccountNo"]);
                        ws.Cells[count, 1].PutValue(dt2.Rows[y]["AerialNo"]);
                        ws.Cells[count, 2].PutValue(dt2.Rows[y]["SepNo"]);

                        ws.Cells[count, 3].PutValue("");
                        ws.Cells[count, 4].PutValue("");
                        ws.Cells[count, 5].PutValue("");
                        //ws.Cells[count, 3].PutValue(dt2.Rows[y]["Receiver"]);
                        //ws.Cells[count, 4].PutValue(dt2.Rows[y]["ToAddress"]);
                        //ws.Cells[count, 5].PutValue(dt2.Rows[y]["ToPhoneNo"]);
                        string pname2 = "";
                        GetNameAndWeight(dt2.Rows[y]["SepNo"].ToString(), ref pname2);
                        ws.Cells[count, 6].PutValue(pname2);
                        ws.Cells[count, 7].PutValue(dt2.Rows[y]["ChargedWt"]);
                        ws.Cells[count, 8].PutValue(dt2.Rows[y]["FlightNo"].ToString());
                    }
                }

            }
        }

        string path = ApplicationInfo.PhysicalApplicationPath + "Temp//" + name + ".xls";

        #region 样式开始 在      wb.Save(path); 之前使用

        Aspose.Cells.Style st = wb.DefaultStyle;
        st.Borders.SetStyle(CellBorderType.Thin);
        st.Borders.DiagonalStyle = CellBorderType.None;
        StyleFlag stg = new StyleFlag();
        stg.All = true;
        ws.Cells.ApplyStyle(st, stg);

        #endregion 样式结束
        wb.Save(path);

        Dc(path, name, "公司出货报表" + DateTime.Parse(datetxt.Value).ToLongDateString());
    }
    protected void haiguan_Click(object sender, ImageClickEventArgs e)
    {

        //海关申请     主单 无分单 单据状态为 3  （飞行中）  + 分单单据 状态为3  

        if (datetxt.Value.Trim() == "")
        {

            Tools.OpenWindow(this, "请选择日期");
            return;
        }
        string sql = @" select  distinct a. TrackingNo as TrackingNo ,'' as Description,AerialNo,sep=0,TrackingNo as Tno,
a.AccountNo as AccountNo,
a.Sender as Sender,
a.FromPhoneNo as FromPhoneNo,
a.FromAddress as FromAddress,
a.Receiver as Receiver,
a.ToPhoneNo as ToPhoneNo,
a.ToAddress as ToAddress ,a.ChargedWt as ChargedWt ,a.companynamepy as companynamepy,a.namepy as namepy,a.tozipcode as zipcode
from   Tbl_WayBill a
left join  Tbl_BillStateRec b on a.TrackingNo=b.SepNo
";
        sql += " where  a.TrackingNo<>'' and a.sep=0  and b.Date >=cast('" + datetxt.Value.Trim() + " 00:00:00 " + "' as datetime) and b.Date< =cast('" + datetxt.Value.Trim() + " 23:59:59 " + "' as datetime) and b.StateID=3";

        string sql2 = @"    union   
select  distinct a. SepNo as TrackingNo ,Description,AerialNo,sep=1,TrackingNo as Tno,
a.ACNo as AccountNo,
a.SendBy as Sender,
a.USATelNo as FromPhoneNo,
a.USAAddress as FromAddress,
a.CHNReceiver as Receiver,
a.CHNTelNo as ToPhoneNo,
a.CHNAddress as ToAddress , a.Weight as ChargedWt ,'' as companynamepy,'' as namepy ,'' as zipcode  
from   Tbl_SepBill a
left join  Tbl_BillStateRec b on a.SepNo=b.SepNo ";
        sql2 += " where  a.SepNo<>''  and b.Date >=cast('" + datetxt.Value.Trim() + " 00:00:00 " + "' as datetime) and b.Date< =cast('" + datetxt.Value.Trim() + " 23:59:59 " + "' as datetime) and b.StateID=3";
        PublicSqlService ps = new PublicSqlService();



        // DAL.SysTemError.SystemLog(sql + sql2);
        DataTable dt = ps.RuntSQL(sql + sql2);

        if (dt.Rows.Count == 0)
        {

            Tools.OpenWindow(this, "当前输入时间并无记录");
            return;
        }


        string name = DateTime.Now.ToString("yyyyMMddHHmmss") + Session["uid"].ToString();



        Workbook wb = new Workbook();
        wb.Worksheets.Clear();
        wb.Worksheets.Add("海关申报报表 - " + datetxt.Value);//New Worksheet1是Worksheet的name
        Worksheet ws = wb.Worksheets[0];


        //1-12 列合并
        ws.Cells.Merge(0, 0, 1, 14);
        ws.Cells[0, 0].PutValue("海关申请报表 - " + DateTime.Parse(datetxt.Value).ToLongDateString());
        ws.Cells[1, 0].PutValue("单号");

        ws.Cells[1, 1].PutValue("袋号");
        ws.Cells[1, 2].PutValue("寄件人");
        ws.Cells[1, 3].PutValue("寄件人电话");
        ws.Cells[1, 4].PutValue("寄件人地址");

        ws.Cells[1, 5].PutValue("收件人"); //分单 主单的
        ws.Cells[1, 6].PutValue("收件人电话");

        ws.Cells[1, 7].PutValue("收件人地址");
        ws.Cells[1, 8].PutValue("品名");

        ws.Cells[1, 9].PutValue("重量");
        //价值
        ws.Cells[1, 10].PutValue("航空号");
        ws.Cells[1, 11].PutValue("分单信息");///
        ws.Cells[1, 12].PutValue("价值");
        ws.Cells[1, 13].PutValue("地址拼音");
        ws.Cells[1, 14].PutValue("名称拼音");
        ws.Cells[1, 15].PutValue("zipcode");
		 ws.Cells[1, 16].PutValue("账号");


        for (int i = 0; i < dt.Rows.Count; i++)
        {


            ws.Cells[i + 2, 0].PutValue(dt.Rows[i]["TrackingNo"]);
            ws.Cells[i + 2, 1].PutValue(dt.Rows[i]["Description"]);
            ws.Cells[i + 2, 2].PutValue(dt.Rows[i]["Sender"]);
            ws.Cells[i + 2, 3].PutValue(dt.Rows[i]["FromPhoneNo"]);
            ws.Cells[i + 2, 4].PutValue(dt.Rows[i]["FromAddress"]);

            ws.Cells[i + 2, 5].PutValue(dt.Rows[i]["Receiver"]);
            ws.Cells[i + 2, 6].PutValue(dt.Rows[i]["ToPhoneNo"]);

            ws.Cells[i + 2, 7].PutValue(dt.Rows[i]["ToAddress"]);
            string pname = "";
            GetNameAndWeight(dt.Rows[i]["TrackingNo"].ToString(), ref pname);
            ws.Cells[i + 2, 8].PutValue(pname);

            ws.Cells[i + 2, 9].PutValue(dt.Rows[i]["ChargedWt"]);
            ws.Cells[i + 2, 10].PutValue(dt.Rows[i]["AerialNo"]);
           

            if (dt.Rows[i]["sep"].ToString().Trim() == "1")
            {
                ws.Cells[i + 2, 11].PutValue(GetMainUser(dt.Rows[i]["Tno"].ToString()));
            }
            else
            {
                ws.Cells[i + 2, 11].PutValue("");
            }

            ws.Cells[i + 2, 12].PutValue(GetJz(dt.Rows[i]["TrackingNo"].ToString()));
            ws.Cells[i + 2, 13].PutValue(dt.Rows[i]["companynamepy"]);
            ws.Cells[i + 2, 14].PutValue(dt.Rows[i]["namepy"]);
            ws.Cells[i + 2, 15].PutValue(dt.Rows[i]["zipcode"]);
	        ws.Cells[i + 2, 16].PutValue(dt.Rows[i]["AccountNo"]);
        }
 

        string path = ApplicationInfo.PhysicalApplicationPath + "Temp//" + name + ".xls";

        #region 样式开始 在      wb.Save(path); 之前使用

        Aspose.Cells.Style st = wb.DefaultStyle;
        st.Borders.SetStyle(CellBorderType.Thin);
        st.Borders.DiagonalStyle = CellBorderType.None;
        StyleFlag stg = new StyleFlag();
        stg.All = true;
        ws.Cells.ApplyStyle(st, stg);

        #endregion 样式结束
        wb.Save(path);
        Dc(path, name, "海关申报报表" + DateTime.Parse(datetxt.Value).ToLongDateString());

    }
    string GetJz(string tno)
    {
        string sql = "select sum(ValueForCustoms) from TblShipment where  TrackingNo='" + tno.Trim() + "'";
        return new PublicSqlService().ExecuteScalar(sql);
    }
    string GetMainUser(string tno)
    {
        string value = "";
        DataTable dt = new PublicSqlService().GetData("Receiver,ToPhoneNo,ToAddress ", "Tbl_WayBill", "TrackingNo='" + tno.Trim() + "'");
        if (dt.Rows.Count > 0)
        {
            value = dt.Rows[0][0].ToString() + "/" + dt.Rows[0][1].ToString() + "/" + dt.Rows[0][2].ToString();
        }

        return value;
    }
    private void GetNameAndWeight(string tno, ref string name)
    {
        DataTable dt = new PublicSqlService().GetData(" top 2 Commodity  ", "TblShipment", "TrackingNo='" + tno.Trim() + "'");

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            // DAL.SysTemError.SystemLog(dt.Rows[i]["Commodity"].ToString());
            name = name + "," + dt.Rows[i]["Commodity"].ToString();
        }

        if (name.Trim() != "")
        {
            name = name.Substring(1);
        }

    }
    private void Dc(string path, string name, string cname)
    {
        FileInfo fi = new FileInfo(path);//excelFile为文件在服务器上的地址
        HttpResponse contextResponse = HttpContext.Current.Response;
        contextResponse.Clear();
        contextResponse.Buffer = true;
        contextResponse.Charset = "utf-8"; //设置了类型为中文防止乱码的出现 
        string filename = HttpUtility.UrlEncode(cname + ".xls");
        contextResponse.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", filename)); //定义输出文件和文件名 
        contextResponse.AppendHeader("Content-Length", fi.Length.ToString());
        contextResponse.ContentEncoding = Encoding.Default;
        contextResponse.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 

        contextResponse.WriteFile(fi.FullName);
        contextResponse.Flush();
        contextResponse.End();
    }
}
